const mssql = require("mssql");

const config = {
  user: 'sa',
  password: '000',
  server: 'localhost',
  database: 'test_db',
  port: 1433,
  options: {
    encrypt: false // Use this if you're on Windows Azure 
    //不设置node会报错 Cannot call write after a stream was destroyed
  },
  pool: {
    min: 0,
    max: 10,
    idleTimeoutMillis: 3000
  }
}

const pool = new mssql.ConnectionPool(config);

const conMsdb = pool.connect();

pool.on('error', err => {
  if (err) { console.error(err); }
});

let querySql = async function (sql, params, callBack) {
  try {
    await conMsdb;
    let ps = new mssql.PreparedStatement(pool);
    if (params != "") {
      for (let index in params) {
        if (typeof params[index] == "number") {
          ps.input(index, mssql.Int);
        } else if (typeof params[index] == "string") {
          ps.input(index, mssql.NVarChar);
        }
      }
    }
    ps.prepare(sql, err => {
      err && console.log(err);

      ps.execute(params, (err, recordset) => {
        callBack(err, recordset);
        ps.unprepare(err => err && console.log(err));
      });
    });
  } catch (err) {
    console.error('SQL error', err);
  }
};

let select = async function (tableName, topNumber, whereSql, params, orderSql, callBack) {
  await conMsdb;
  try {
    let ps = new mssql.PreparedStatement(pool);
    let sql = "select * from " + tableName + " ";
    if (topNumber != "") {
      sql = "select top(" + topNumber + ") * from " + tableName + " ";
    }
    sql += whereSql + " ";
    sql += orderSql;
    console.log(sql);
    if (params != "") {
      for (let index in params) {
        if (typeof params[index] == "number") {
          ps.input(index, mssql.Int);
        } else if (typeof params[index] == "string") {
          ps.input(index, mssql.NVarChar);
        }
      }
    };
    console.log(ps)
    ps.prepare(sql, err => {
      if (err)
        console.log(err);

      ps.execute(params, (err, recordset) => {
        callBack(err, recordset);
        ps.unprepare(err => err && console.log(err));
      });
    });
  } catch (err) {
    console.error('SQL error', err);
  }
};

let selectAll = async function (tableName, callBack) {
  await conMsdb;
  try {
    let ps = new mssql.PreparedStatement(pool);
    let sql = "select * from " + tableName + " ";
    ps.prepare(sql, err => {
      if (err)
        console.log(err);
      ps.execute("", (err, recordset) => {
        callBack(err, recordset);
        ps.unprepare(err => err && console.log(err));
      });
    });
  } catch (err) {
    console.error('SQL error', err);
  }
};

let add = async function (addObj, tableName, callBack) {
  await conMsdb;
  try {
    let ps = new mssql.PreparedStatement(pool);
    let sql = "insert into " + tableName + "(";
    if (addObj != "") {
      for (let index in addObj) {
        if (typeof addObj[index] == "number") {
          ps.input(index, mssql.Int);
        } else if (typeof addObj[index] == "string") {
          ps.input(index, mssql.NVarChar);
        }
        sql += index + ",";
      }
      sql = sql.substring(0, sql.length - 1) + ") values(";
      for (let index in addObj) {
        if (typeof addObj[index] == "number") {
          sql += addObj[index] + ",";
        } else if (typeof addObj[index] == "string") {
          sql += "'" + addObj[index] + "'" + ",";
        }
      }
    }
    sql = sql.substring(0, sql.length - 1) + ")";

    ps.prepare(sql, err => {
      if (err)
        console.log(err);

      ps.execute(addObj, (err, recordset) => {
        callBack(err, recordset);
        ps.unprepare(err => err && console.log(err));
      });
    });
  } catch (err) {
    console.error('SQL error', err);
  }
};

let update = async function (updateObj, whereObj, tableName, callBack) {
  await conMsdb;
  try {
    let ps = new mssql.PreparedStatement(pool);
    let sql = "update " + tableName + " set ";
    if (updateObj != "") {
      for (let index in updateObj) {
        if (typeof updateObj[index] == "number") {
          ps.input(index, mssql.Int);
          sql += index + "=" + updateObj[index] + ",";
        } else if (typeof updateObj[index] == "string") {
          ps.input(index, mssql.NVarChar);
          sql += index + "=" + "'" + updateObj[index] + "'" + ",";
        }
      }
    }
    sql = sql.substring(0, sql.length - 1) + " where ";
    if (whereObj != "") {
      for (let index in whereObj) {
        if (typeof whereObj[index] == "number") {
          ps.input(index, mssql.Int);
          sql += index + "=" + whereObj[index] + " and ";
        } else if (typeof whereObj[index] == "string") {
          ps.input(index, mssql.NVarChar);
          sql += index + "=" + "'" + whereObj[index] + "'" + " and ";
        }
      }
    }
    sql = sql.substring(0, sql.length - 5);

    ps.prepare(sql, err => {
      if (err)
        console.log(err);

      ps.execute(updateObj, (err, recordset) => {
        callBack(err, recordset);
        ps.unprepare(err => err && console.log(err));
      });
    });
  } catch (err) {
    console.error('SQL error', err);
  }
};

let del = async function (whereSql, params, tableName, callBack) {
  await conMsdb;
  try {
    let ps = new mssql.PreparedStatement(pool);
    let sql = "delete from " + tableName + " ";
    if (params != "") {
      for (let index in params) {
        if (typeof params[index] == "number") {
          ps.input(index, mssql.Int);
        } else if (typeof params[index] == "string") {
          ps.input(index, mssql.NVarChar);
        }
      }
    }
    sql += whereSql;
    ps.prepare(sql, err => {
      if (err)
        console.log(err);

      ps.execute(params, (err, recordset) => {
        callBack(err, recordset);
        ps.unprepare(err => err && console.log(err));
      });
    });
  } catch (err) {
    console.error('SQL error', err);
  }
};


exports.add = add;
exports.del = del;
exports.update = update;
exports.select = select;
exports.selectAll = selectAll;
exports.querySql = querySql;